
import pandas as pd
import glob
import os

def process_file(input_file):
    # 读取原始数据
    raw_df = pd.read_csv(input_file)
    
    # 使用pivot_table转换数据结构
    table = raw_df.pivot_table(
        index=['date', 'hour'],
        columns='type',
        values='1001A',
        aggfunc='first'
    ).reset_index()
    
    # 重命名列并处理列名格式
    table = table.rename(columns={
        'PM2.5': 'PM2.5',
        'PM10': 'PM10',
        'SO2_24h': 'SO2_24h',
        'CO_24h': 'CO_24h',
        'O3_24h': 'O3_24h',
        'PM2.5_24h': 'PM2.5_24h',
        'PM10_24h': 'PM10_24h'
    })
    
    # 定义目标列顺序
    target_columns = ['date','hour','AQI','PM2.5','PM2.5_24h','O3','SO2_24h','SO2','PM10', 'PM10_24h', 'O3_24h', 'CO_24h', 'CO']
    table = table.reindex(columns=target_columns)
    
    # 向前填充24小时均值列
    table['PM2.5_24h'] = table['PM2.5'].fillna(method='ffill').rolling(24, min_periods=1).mean()
    table['PM10_24h'] = table['PM10'].fillna(method='ffill').rolling(24, min_periods=1).mean()
    
    # 生成输出文件名
    output_file = os.path.join('data', 'result', os.path.basename(input_file))
    # 确保输出目录存在
    os.makedirs(os.path.dirname(output_file), exist_ok=True)
    # 保存处理后的数据
    table.to_csv(output_file, index=False)

# 获取所有需要处理的CSV文件
csv_files = glob.glob(os.path.join('data', 'selected_sites', 'china_sites_*.csv'))

# 处理每个文件
for file in csv_files:
    process_file(file)